// License Notification
//======================================================
// Copyright (c) 2010 Channel Intelligence Inc.
// All rights reserved. This program and the accompanying materials
// are made available under the terms of the Eclipse Public License v1.0
// which accompanies this distribution, and is available at
// http://www.eclipse.org/legal/epl-v10.html
//
// ======================================================




using System;
using System.Collections.Generic;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Reflection;
using CIP4.Item;
using CIP4.Common;
using CIP4.Simple;
using CIP4.RulesEngine;

namespace CIP4.DataProvider.Sql
{
    // have a look at
    //     http://www.codeproject.com/KB/database/SqlFileStream.aspx?display=Print 
    // for ideas on how to handle streams

    public class DataProvider : CIP4.Simple.DataProvider,  IDataProvider
    {
        #region Member Variables

        IDataProvider Cache { get; set; }

        private string _Host;

        private string _Database;

        private string _ApplicationName;

        private string _UserId;

        private String _Password;

        private int MAXRETRIES = 6;

        private int RETRYBACKOFFDELTA = 4; // Results in waits of 0.004, 0.016, 0.064, 0.256, 1.024, and 4.096 seconds


        #endregion

        #region Constants

        enum DBVersionValues
        {
            Release = 1,
            Version = 4,
            Build = 1,
            Revision = 4,
            Active = 1
        };

        public int DBVersion = 0;

        public int DBRevision = 0;

        Regex ConnectionStringRx = new Regex("^Connect Timeout=60;Data Source=[\\w]+;Initial Catalog=[\\w]+;Integrated Security=SSPI$");

        #region Column Names

        private const string COLUMN_ITEM_ID = "ItemID";
        private const string COLUMN_STATUS_ID = "StatusID";
        private const string COLUMN_ITEM_STATUS_ID = "ItemStatusID";
        private const string COLUMN_TRANSACTION_ID = "TransactionID";
        private const string COLUMN_CREATED_TRANSACTION_ID = "CreatedTransactionID";
        private const string COLUMN_DELETED_TRANSACTION_ID = "DeletedTransactionID";
        private const string COLUMN_LAST_MOD_TRANSACTION_ID = "LastModTransactionID";
        private const string COLUMN_OBJECT_TEXT = "ObjectText";
        private const string COLUMN_HASH_VALUE = "HashValue";
        private const string COLUMN_CHANGE_HISTORY_TEXT = "ChangeHistoryText";
        private const string COLUMN_STATUS_MESSAGE = "StatusMessage";
        private const string COLUMN_STATE_MESSAGE = "StateMessage";
        private const string COLUMN_ID = "ID";
        private const string COLUMN_EXTERNAL_ID = "ExternalID";
        private const string COLUMN_ITEMTYPEVERSION = "ItemtypeVersion";
        private const string COLUMN_ENTERED_DATE = "EnteredDate";
        private const string COLUMN_LAST_MOD_DATE = "LastModDate";
        private const string COLUMN_COLLECTION_ID = "CollectionID";
        private const string COLUMN_ITEM_MESSAGE_SEVERITY_TYPE_ID = "ItemMessageSeverityTypeID";

        #endregion

        #region Parameter Names

        private const string PARAM_COLLECTION_PUBLISHED_ID = "@CollectionPublishedID";
        private const string PARAM_ITEM_STATUS_ID = "@ItemStatusId";
        private const string PARAM_OBJECT_TEXT = "@ObjectText";
        private const string PARAM_HASH_VALUE = "@HashValue";
        private const string PARAM_PAGE_NUM = "@PageNumber";
        private const string PARAM_PAGE_SIZE = "@PageSize";
        private const string PARAM_FILTER_CLAUSE = "@FilterClause";
        private const string PARAM_EXTERNALID = "@externalID ";
        private const string PARAM_ORDER_BY = "@OrderBy";
        private const string PARAM_CHANGE_HISTORY_TEXT = "@ChangeHistoryText";
        private const string PARAM_COLLECTION_ID = "@CollectionID";
        private const string PARAM_ITEMSTATUS_ID = "@ItemStatusID";
        private const string PARAM_ITEM_ID = "@ItemID";
        private const string PARAM_EXTERNAL_ID = "@ExternalID";
        private const string PARAM_MESSAGE_SEVERITY_TYPE_ID = "@MessageSeverityTypeID";

        #endregion

        #endregion

        #region Public Features

        public string ConnectionString 
        { 
            get 
            { 
                if (!string.IsNullOrEmpty(_ApplicationName))
                    return string.Format("Connect Timeout=60;Data Source={0};Initial Catalog={1};Application Name={2}{3}", 
                        _Host, _Database, _ApplicationName, UserIdPassword);
                return string.Format("Connect Timeout=60;Data Source={0};Initial Catalog={1}{2}", 
                    _Host, _Database, UserIdPassword); 
            } 
        }

        private string UserIdPassword
        {
            get
            {
                string result = string.Empty;
                if (!string.IsNullOrEmpty(_UserId))
                {
                    result = string.Format(";User Id={0}", _UserId);
                    if (!string.IsNullOrEmpty(_UserId))
                        result = string.Format(";Password={0}", _Password);
                }
                else if (!string.IsNullOrEmpty(_Password))
                    throw new ArgumentException("Password has been specified without user id");
                else
                    result = ";Integrated Security=SSPI";
                return result;
            }
        }

        public DataProvider (CIP4ConnectionAttribute DataProviderConnection, IDataProvider cachingProvider, LoggerDelegate logger):
            base (DataProviderConnection)
        {
            Cache = cachingProvider;
            if (DataProviderConnection is CIP4SqlConnectionAttribute)
            {
                _Host = ((CIP4SqlConnectionAttribute)DataProviderConnection).Host;
                _Database = ((CIP4SqlConnectionAttribute)DataProviderConnection).Database;
                _ApplicationName = ((CIP4SqlConnectionAttribute)DataProviderConnection).ApplicationName;
                _Password = ((CIP4SqlConnectionAttribute)DataProviderConnection).Password;
                _UserId = ((CIP4SqlConnectionAttribute)DataProviderConnection).UserId;
                VerifyDatabaseVersion(logger);
                LoggerWrapper(MajorEnum.Trace, MinorEnum.InitializingProvider, 
                    "Initalizing Provider: {0}", new object[] { ConnectionString }, logger);
            }
            else
            {
                LoggerWrapper(MajorEnum.Error, MinorEnum.UnexpectedConnectionType, 
                    "Initalizing SqlDataProvider: Given an unacceptable connection type: {0}", new object[] { DataProviderConnection }, logger);
            }
        }

        public void DeleteCollection(CollectionType collection, LoggerDelegate logger)
        {
            if (DBVersion > (int)DBVersionValues.Version)
                throw new ArgumentException(string.Format("DeleteCollection is not possible because the database version.revision is not compatible with this version of the SqlDataProvider, expecting {0}.{1} datbase is {2}.{3}", DBVersion, DBRevision, (int)DBVersionValues.Version, (int)DBVersionValues.Revision)); 

            if (!LoggerWrapper(MajorEnum.Trace, MinorEnum.ActionStart,
                "Started {0}", new object[] { MinorEnum.DeleteCollection }, logger))
                return;

            try
            {
                if (Cache != null)
                    Cache.DeleteCollection(collection, logger);
                using (SqlConnection connection = getAndOpenConnection(logger))
                {

                    SqlCommand cmd = connection.CreateCommand();
                    cmd.CommandTimeout = 180;

                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = "uspPurgeCollection0404";

                    cmd.Parameters.AddWithValue(PARAM_COLLECTION_ID, collection.ExternalIDGuid());

                    cmd.ExecuteNonQuery();
                    LoggerWrapper(MajorEnum.Information, MinorEnum.DeleteCollection,
                        "Collection deleted {0}", new object[] { collection }, logger);
                }
            }

            catch (Exception ex)
            {
                LoggerWrapper(MajorEnum.Error, MinorEnum.ErrorDeletingCollection, "Unexpected Error Deleting Collection: {0}", new object[] { ex }, logger);
            }
        }

        public override IEnumerable<object> GetItems(CollectionType collection, IBooleanValue selectionExpression, CursorType cursor)
        {
            List<object> items = new List<object>();
            if (!LoggerWrapper(MajorEnum.Trace, MinorEnum.ActionStart,
                "Started {0}", new object[] { MinorEnum.GetItems }, cursor.Logger))
                return items;

            try
            {
                ISerializerType serializer = cursor.GetSerializer(SerializerKindEnum.CIP4Xml);

                string lookForExternalId = GetExternalIdValueFromSelectionExpression(selectionExpression, serializer);

                if (Cache != null && !string.IsNullOrEmpty(lookForExternalId))
                {
                    foreach (object item in Cache.GetItems(collection, selectionExpression, cursor))
                    {
                        items.Add(item);
                        LoggerWrapper(MajorEnum.Trace, MinorEnum.CacheHit, "Found {0} in the cache", new object[] { lookForExternalId }, cursor.Logger);
                        return items;
                    }
                }

                if (!string.IsNullOrEmpty(lookForExternalId)  && DBVersion >= 4)
                    items = GetItemsFromTheTableViaExternalID(collection, lookForExternalId, selectionExpression, cursor);
                else 
                    items = GetItemsFromTheTable(collection, selectionExpression, cursor);

                return items;
            }
            finally
            {
                LoggerWrapper(MajorEnum.Information, MinorEnum.GetItems,
                    "Get {0} items from {1}", new object[] { items.Count, collection }, cursor.Logger);
            }
        }

        public override void PurgeItems(IEnumerable<ExternalIDType> externalIDs, CollectionType collection, GetSerializerDelegate getSerializer)
        {
            if (DBVersion > (int)DBVersionValues.Version)
                throw new ArgumentException(string.Format("PurgeItems is not possible because the database version.revision is not compatible with this version of the SqlDataProvider, expecting {0}.{1} datbase is {2}.{3}", DBVersion, DBRevision, (int)DBVersionValues.Version, (int)DBVersionValues.Revision));
            ISerializerType serializer = getSerializer(SerializerKindEnum.CIP4Xml);
            if (!LoggerWrapper(MajorEnum.Trace, MinorEnum.ActionStart,
                "Started {0}", new object[] { MinorEnum.PurgeItems }, serializer.Logger))
                return;

            int itemCount = 0;
            
            try
            {
                if (Cache != null)
                    Cache.PurgeItems(externalIDs, collection, getSerializer);
                foreach (ExternalIDType externalID in externalIDs)
                {
                    ExecutePurgeCmd(externalID.ToString(), collection, serializer.Logger);
                    
                    RemoveItemsStreamResolver(serializer, collection, externalID.ToString());

                    itemCount++;
                }
            }
            catch (Exception ex)
            {
                LoggerWrapper(MajorEnum.Error, MinorEnum.ErrorPurgingItems,
                    "PurgeItems Error: {0}", new object[] { ex }, serializer.Logger);
                throw;
            }
            LoggerWrapper(MajorEnum.Information, MinorEnum.PurgeItems,
                "{0} items deleted from {1}", new object[] { itemCount, collection.Name }, serializer.Logger);
        }

        public void SaveItems(IEnumerable<object> items, CollectionType collection, GetSerializerDelegate getSerializer)
        {
            if (DBVersion > (int)DBVersionValues.Version)
                throw new ArgumentException(string.Format("SaveItems is not possible because the database version.revision is not compatible with this version of the SqlDataProvider, expecting {0}.{1} datbase is {2}.{3}", DBVersion, DBRevision, (int)DBVersionValues.Version, (int)DBVersionValues.Revision));
            ISerializerType serializer = getSerializer(SerializerKindEnum.CIP4Xml);
            if (!LoggerWrapper(MajorEnum.Trace, MinorEnum.ActionStart,
                "Started {0}", new object[] { MinorEnum.SaveItems }, serializer.Logger))
                return;

            int itemCount = 0;

            try
            {
                foreach (object objectItem in items)
                {
                    SaveItem(objectItem, collection, serializer);

                    itemCount++;
                }
                if (Cache != null)
                    Cache.SaveItems(items, collection, getSerializer);
            }
            catch (Exception ex)
            {
                LoggerWrapper(MajorEnum.Error, MinorEnum.ErrorSavingItems, "SaveItems Error: {0}", new object[] { ex }, serializer.Logger);
            }
            LoggerWrapper(MajorEnum.Information, MinorEnum.SaveItems,
                "{0} items saved in {1}", new object[] { itemCount, collection.Name }, serializer.Logger);
        }

        public void Terminate(LoggerDelegate logger)
        {
        }

        #endregion

        #region Private Methods

        #region Stream Handling

        // This implementation is using blobs not streamfiles - this appears to be more efficient for blobs < 1MB
        // as compared to streamfiles - streamfiles and blobs are about the same in the 1-4MB range beyond 4MB (or so) 
        // streamfiles are much more efficient. 
        // See http://www.codeproject.com/KB/database/SqlFileStream.aspx?display=Print for a comparison

        private void RemoveItemsStreamResolver(ISerializerType serializer, CollectionType collection, string externalID)
        {
            foreach (KeyValuePair<string, KeyValuePair<PropertyInfo, object>> streamSeed in serializer.StreamSeeds)
            {
                SqlCommand cmd;
                SqlConnection connection = null;
                int retryCount = 0;
                do
                {
                    try
                    {
                        using (connection = getAndOpenConnection(serializer.Logger))
                        {

                            cmd = connection.CreateCommand();
                            cmd.CommandTimeout = 180;

                            cmd.CommandType = System.Data.CommandType.Text;
                            cmd.CommandText = string.Format("delete [BinaryData] where [CollectionID] = @CollectionID AND [ExternalID] = @ExternalID AND [Path] = @Path",
                                collection.ExternalIDString(), externalID, streamSeed.Key);

                            cmd.Parameters.AddWithValue(PARAM_COLLECTION_ID, collection.ExternalIDGuid());
                            cmd.Parameters.AddWithValue("@ExternalID", externalID);
                            cmd.Parameters.AddWithValue("@Path", streamSeed.Key);

                            cmd.ExecuteNonQuery();

                            break;
                        }
                    }
                    catch (Exception ex)
                    {
                        retryCount += 1;

                        if (ex.Message.StartsWith("The DataSource specified in the conection is not reachable"))
                            LoggerWrapper(MajorEnum.Warning, MinorEnum.RetryAccessingDataStore,
                                "Retrying GetItemsFromTheTable because of {0}", new object[] { ex.Message }, serializer.Logger);
                        else if (ex.Message.EndsWith("TimedOut"))
                            LoggerWrapper(MajorEnum.Warning, MinorEnum.RetryAccessingDataStore,
                                "Retrying GetItemsFromTheTable because of {0}", new object[] { ex.Message }, serializer.Logger);
                        else
                            LoggerWrapper(MajorEnum.Error, MinorEnum.ErrorGettingItemsFromProvider, "Unexpected error removing a stream: {0}" , new object[] { ex }, serializer.Logger);

                        Thread.Sleep((int)Math.Pow(RETRYBACKOFFDELTA, retryCount));
                    }
                } while (retryCount < MAXRETRIES);
                if (retryCount == MAXRETRIES)
                {
                    LoggerWrapper(MajorEnum.Error, MinorEnum.RetryCountExceeded, "Retry count exceeded for collection {0}", new object[] { collection.Name }, serializer.Logger);
                }
            }

        }

        private void GetItemsStreamResolver(ISerializerType serializer, CollectionType collection, string externalID)
        {
            string collectionFolder = collection.ExternalIDString();
            foreach (KeyValuePair<string, KeyValuePair<PropertyInfo, object>> streamSeed in serializer.StreamSeeds)
            {
                if (streamSeed.Value.Value == null)
                    continue;
                Stream str = new CIP4FileStream(collection.ExternalIDGuid(), externalID, streamSeed.Key, getAndOpenConnection, serializer.Logger);
                streamSeed.Value.Key.SetValue(streamSeed.Value.Value, str, new object[] { });
            }

        }

        private void SaveItemsStreamResolver(ISerializerType serializer, CollectionType collection, SqlConnection connection, SqlTransaction transaction, string externalID)
        {
            foreach (KeyValuePair<string, KeyValuePair<PropertyInfo, object>> streamSeed in serializer.StreamSeeds)
            {
                if (streamSeed.Value.Value == null)
                    continue;
                Stream source = (Stream)streamSeed.Value.Value;
                if (source != null)
                {
                    {
                        {
                            using (SqlCommand command = CreateCommand(connection, transaction))
                            {
                                command.CommandText = "INSERT INTO [BinaryData] ([CollectionID], [ExternalID], [Path], [Data]) VALUES (@CollectionID, @ExternalID, @Path, @Data)";
                                command.CommandType = System.Data.CommandType.Text;

                                command.Parameters.AddWithValue(PARAM_COLLECTION_ID, collection.ExternalIDGuid());
                                command.Parameters.AddWithValue("@ExternalID", externalID);
                                command.Parameters.AddWithValue("@Path", streamSeed.Key);

                                SqlParameter parameter = new System.Data.SqlClient.SqlParameter("@Data", System.Data.SqlDbType.VarBinary);

                                byte[] buffer = new byte[source.Length];

                                source.Read(buffer, 0, (int)source.Length);

                                parameter.Value = buffer;

                                command.Parameters.Add(parameter);

                                int rowinserted = command.ExecuteNonQuery();

                                if (rowinserted != 1)
                                {
                                    LoggerWrapper(MajorEnum.Error, MinorEnum.ErrorSavingItems, "Unable to save the stream property {0} for item {1}", new object[] { streamSeed.Key, externalID }, serializer.Logger);
                                }

                            }
                        }
                    }
                    source.Close();
                    source.Dispose();
                }
            }

        }

        #endregion

        #region GetItem Helpers

        class QueryType
        {
            public bool NotViable { get; set; }

            public int ClauseCount { get; set; }

            public string QueryText
            {
                get
                {
                    return NotViable? string.Empty : QueryHeaderText + " WHERE item.CollectionID = '{0}' AND " + QueryClauseText;
                }
            }

            public string QueryHeaderText { get; set; }

            public string QueryClauseText { get; set; }

            private const string QueryClauseTextLiteral =
                @"        i{0}.CollectionID = '{{0}}' AND 
        item.ExternalID = i{0}.OwnerExternalID AND
        i{0}.PropertyName = '{1}' AND
        i{0}.PropertyValue = '{2}' 
";

            public QueryType()
            {
                QueryHeaderText = string.Empty;
                QueryClauseText = string.Empty;
            }

            public void AddClause(string propertyName, object propertyValue)
            {
                ClauseCount++;
                QueryHeaderText += string.Format(", ItemIndex i{0} ", ClauseCount);
                QueryClauseText += (ClauseCount > 1? " AND " : string.Empty) + 
                    string.Format(QueryClauseTextLiteral, ClauseCount, propertyName, propertyValue);
            }

            public void AddExternalIDClause(object value)
            {
                ClauseCount++;
                QueryClauseText += (ClauseCount > 1 ? " AND " : string.Empty) +
                    string.Format(" item.ExternalID = '{0}' ", value);
            }
        }

        /// <summary>
        /// Building things like
        /// 
        /// select * from item i, ItemIndex i1, ItemIndex i2 where
        ///       i.CollectionID = i1.CollectionID AND 
        ///       i.ExternalID = i1.OwnerExternalID AND
        ///       i1.PropertyName = 'Origin' AND
        ///       i1.PropertyValue = 'Rhodesia' AND
        ///       i.CollectionID = i2.CollectionID AND 
        ///       i.ExternalID = i2.OwnerExternalID AND
        ///       i2.PropertyName = 'Status' AND
        ///       i2.PropertyValue = 'Current' 
        /// 
        /// </summary>
        /// <param name="sqlQuery"></param>
        /// <param name="selectionExpression"></param>
        /// <param name="collection"></param>
        /// <param name="logger"></param>
        /// <returns></returns>
        private QueryType BuildQuery(QueryType sqlQuery, IBooleanValue selectionExpression, CollectionType collection, ISerializerType serializer)
        {
            if (selectionExpression == null)
                return sqlQuery;
            if (selectionExpression is LogicalExpressionType)
                return BuildQuery(sqlQuery, (LogicalExpressionType)selectionExpression, collection, serializer);
            else
                return BuildQuery(sqlQuery, (BooleanExpressionType)selectionExpression, collection, serializer);
        }
        
        private QueryType BuildQuery(QueryType sqlQuery, LogicalExpressionType selectionExpression, CollectionType collection, ISerializerType serializer)
        {
            if (selectionExpression == null)
                return sqlQuery;
            if (selectionExpression.ExpressionOperator == LogicalOperatorsEnum.AND)
            {
                foreach (int index in new int[] { 0, 1 })
                {
                    if (selectionExpression.Operands[index] is LogicalExpressionType)
                        BuildQuery(sqlQuery, (LogicalExpressionType)selectionExpression.Operands[index], collection, serializer);
                    else if (selectionExpression.Operands[index] is BooleanExpressionType)
                        BuildQuery(sqlQuery, (BooleanExpressionType)selectionExpression.Operands[index], collection, serializer);
                    else if (selectionExpression.Operands[index] is BooleanAtomType)
                        BuildQuery(sqlQuery, (BooleanAtomType)selectionExpression.Operands[index], collection, serializer);
                    else
                        LoggerWrapper(MajorEnum.Error, MinorEnum.InvalidQuery, "Logical expression parameters must be a logical expression, a boolean expression or a boolean atom - scanning: {0}", new object[] { selectionExpression.Operands[index].ToString() }, serializer.Logger);
                }
            }
            else if (selectionExpression.ExpressionOperator == LogicalOperatorsEnum.OR)
            {
                sqlQuery.NotViable = true;
            }
            else if (selectionExpression.ExpressionOperator == LogicalOperatorsEnum.NOT)
            {
                sqlQuery.NotViable = true;
            }
            return sqlQuery;
        }

        private object LiteralString(AtomType atom)
        {
            if (!atom.IsLiteral)
                throw new ArgumentException(string.Format("The atom {0} is of an unexpected type - literal expected", atom.ToString()));
            if (atom is DateTimeAtomType)
                return DateTime.SpecifyKind(((DateTimeAtomType)atom).AtomValue.Value, DateTimeKind.Local);
            if (atom is StringAtomType)
                return ((StringAtomType)atom).AtomValue;
            if (atom is IntegerAtomType)
                return ((IntegerAtomType)atom).AtomValue.Value;
            if (atom is BooleanAtomType)
                return ((BooleanAtomType)atom).AtomValue.Value;
            if (atom is FloatAtomType)
                return ((FloatAtomType)atom).AtomValue.Value;
            else
                return atom.ToString();
        }

        private QueryType BuildQuery(QueryType sqlQuery, RulesEngine.BooleanExpressionType selectionExpression, CollectionType collection, ISerializerType serializer)
        {
            AtomType namedValue =
                selectionExpression.Operands[0].IsNamedValue ? (AtomType)selectionExpression.Operands[0] :
                selectionExpression.Operands[1].IsNamedValue ? (AtomType)selectionExpression.Operands[1] : null;

            AtomType literalValue =
                selectionExpression.Operands[0].IsLiteral ? (AtomType)selectionExpression.Operands[0] :
                selectionExpression.Operands[1].IsLiteral ? (AtomType)selectionExpression.Operands[1] : null;

            if (namedValue == null || literalValue == null)
            {
                LoggerWrapper(MajorEnum.Error, MinorEnum.InvalidQuery, "Invalid boolean expression. Each boolean clause must consist of a literal and a named value: {0}", new object[] { selectionExpression.ToString() }, serializer.Logger);
                return sqlQuery;
            }

            string[] parts = namedValue.AtomName.Split('.');

            string path = parts[parts.Length - 1];

            string externalIDValue = string.Empty;
            
            KeyValuePair<string, int> kvp = serializer.GetKeys().FirstOrDefault(kv => kv.Key == path);
            // must be either the externalid or the first part of the key
            // this is still only a partial solution as it will ignore subsequent key components
            if (!(path.ToLower() == "externalid" || kvp.Key == path && serializer.GetKeys().First(k => k.Value == kvp.Value).Key == kvp.Key))
                return sqlQuery;

            switch (selectionExpression.ExpressionOperator)
            {
                case CIP4.RulesEngine.BooleanOperatorsEnum.EQL:
                    if (parts.Length == 1 && parts[0].ToLower() == "externalid")
                    {
                        sqlQuery.AddExternalIDClause(LiteralString(literalValue));
                    }
                    else if (kvp.Value == 0)
                    {
                        // ToDo: allow for multi-part externalids - currently the code will only search via
                        // externalID if it has one part
                        if (serializer.GetKeys().Count(kvpe => kvpe.Value == 0) == 1)
                            sqlQuery.AddExternalIDClause(ExternalIDType.ExternalIDEscape(LiteralString(literalValue).ToString()));
                        else
                            sqlQuery.NotViable = true;
                    }
                    else
                    {
                        sqlQuery.AddClause(path, LiteralString(literalValue));
                    }
                    break;
                case CIP4.RulesEngine.BooleanOperatorsEnum.NEQ:
                case CIP4.RulesEngine.BooleanOperatorsEnum.GEQ:
                case CIP4.RulesEngine.BooleanOperatorsEnum.GTR:
                case CIP4.RulesEngine.BooleanOperatorsEnum.LEQ:
                case CIP4.RulesEngine.BooleanOperatorsEnum.LSS:
                case CIP4.RulesEngine.BooleanOperatorsEnum.STARTSWITH:
                case CIP4.RulesEngine.BooleanOperatorsEnum.CONTAINS:
                    sqlQuery.NotViable = true;
                    break;
            }
            return sqlQuery;
        }

        private QueryType BuildQuery(QueryType sqlQuery, RulesEngine.BooleanAtomType selectionExpression, CollectionType collection, ISerializerType serializer)
        {
            sqlQuery.NotViable = true;
            return sqlQuery;
        }

        private string BuildXPathQuery(CollectionType collection, IBooleanValue selectionExpression, CursorType cursor)
        {
            string whereClause = selectionExpression == null? string.Empty : ((RulesEngine.BooleanExpressionType)selectionExpression).XPathString(cursor.Logger);
            whereClause = string.IsNullOrEmpty(whereClause) ? string.Empty : " AND " + whereClause;
            return whereClause;
        }

        private List<object> GetItemsFromTheTable(CollectionType collection, IBooleanValue selectionExpression, CursorType cursor)
        {
            List<object> result = new List<object>();
            SqlCommand cmd;
            SqlConnection connection = null;
            int retryCount = 0;
            do
            {
                try
                {
                    ISerializerType serializer = cursor.GetSerializer(SerializerKindEnum.CIP4Xml);
                    string filterClause = string.Empty; 
                    if (DBVersion < 4)
                    {
                        filterClause = BuildXPathQuery(collection, selectionExpression, cursor);
                    }
                    else
                    {
                        QueryType query = BuildQuery(new QueryType(), selectionExpression, collection, serializer);
                        if (!query.NotViable && query.ClauseCount > 0)
                            filterClause = string.Format(query.QueryText, collection.ExternalIDGuid().ToString());
                        else
                        {
                            
                            if (cursor.FailOnLinearSearch)
                                if (!LoggerWrapper(MajorEnum.Error, MinorEnum.FailOnLinearSearch,
                                    "Failed on linear search accessing {0}", new object[] { collection }, cursor.Logger))
                                    return new List<object>();

                            filterClause = string.Format(" WHERE item.CollectionID = '{0}'", collection.ExternalIDGuid().ToString());
                        }
                    }
                    using (connection = getAndOpenConnection(cursor.Logger))
                    {

                        cmd = connection.CreateCommand();
                        cmd.CommandTimeout = 180;

                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.CommandText = DBVersion < 4 ? "uspGetCollectionItems" : "uspGetItems0404";

                        cmd.Parameters.AddWithValue(PARAM_COLLECTION_PUBLISHED_ID, collection.ExternalIDGuid());
                        cmd.Parameters.AddWithValue(PARAM_PAGE_NUM, cursor.PageNumber);
                        cmd.Parameters.AddWithValue(PARAM_PAGE_SIZE, cursor.PageSize);
                        cmd.Parameters.AddWithValue(PARAM_FILTER_CLAUSE, filterClause);

                        result = populateItemsFromDataReader(cmd.ExecuteReader(), serializer, collection, selectionExpression);

                        //cursor.PageNumber += 1;
                        break;
                    }
                }
                catch (Exception ex)
                {
                    retryCount += 1;

                    if (ex.Message.StartsWith("Unable to keep going on Error - Failed on linear search accessing"))
                        throw;
                    else if (ex.Message.StartsWith("The DataSource specified in the connection is not reachable"))
                        LoggerWrapper(MajorEnum.Warning, MinorEnum.RetryAccessingDataStore, 
                            "Retrying GetItemsFromTheTable because of {0}", new object[] { ex.Message }, cursor.Logger);
                    else if (ex.Message.EndsWith("TimedOut"))
                        LoggerWrapper(MajorEnum.Warning, MinorEnum.RetryAccessingDataStore,
                            "Retrying GetItemsFromTheTable because of {0}", new object[] { ex.Message }, cursor.Logger);
                    else
                        LoggerWrapper(MajorEnum.Error, MinorEnum.ErrorGettingItemsFromProvider, "Unexpected error getting items: {0}", new object[] { ex }, cursor.Logger);

                    Thread.Sleep((int)Math.Pow(RETRYBACKOFFDELTA, retryCount));
                }
            } while (retryCount < MAXRETRIES);
            if (retryCount == MAXRETRIES)
            {
                LoggerWrapper(MajorEnum.Error, MinorEnum.RetryCountExceeded, "Retry count exceeded for collection {0}", new object[] { collection.Name }, cursor.Logger);
            }
            if (Cache != null)
                Cache.SaveItems(result, collection, cursor.GetSerializer);

            return result;
        }

        private List<object> GetItemsFromTheTableViaExternalID(CollectionType collection, string externalID, IBooleanValue selectionExpression, CursorType cursor)
        {
            List<object> result = new List<object>();
            SqlCommand cmd;
            SqlConnection connection = null;
            int retryCount = 0;
            do
            {
                try
                {
                    ISerializerType serializer = cursor.GetSerializer(SerializerKindEnum.CIP4Xml);

                    using (connection = getAndOpenConnection(cursor.Logger))
                    {

                        cmd = connection.CreateCommand();
                        cmd.CommandTimeout = 180;

                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.CommandText = "uspGetItemViaExternalID0404";

                        cmd.Parameters.AddWithValue(PARAM_COLLECTION_PUBLISHED_ID, collection.ExternalIDGuid());
                        cmd.Parameters.AddWithValue(PARAM_EXTERNALID, externalID);

                        result = populateItemsFromDataReader(cmd.ExecuteReader(), serializer, collection, selectionExpression);

                        //cursor.PageNumber += 1;
                        break;
                    }
                }
                catch (Exception ex)
                {
                    retryCount += 1;

                    if (ex.Message.StartsWith("The DataSource specified in the connection is not reachable"))
                        LoggerWrapper(MajorEnum.Warning, MinorEnum.RetryAccessingDataStore,
                            "Retrying GetItemsFromTheTable because of {0}", new object[] { ex.Message }, cursor.Logger);
                    else if (ex.Message.EndsWith("TimedOut"))
                        LoggerWrapper(MajorEnum.Warning, MinorEnum.RetryAccessingDataStore,
                            "Retrying GetItemsFromTheTable because of {0}", new object[] { ex.Message }, cursor.Logger);
                    else
                        LoggerWrapper(MajorEnum.Error, MinorEnum.ErrorGettingItemsFromProvider, "Unexpected error getting items: {0}", new object[] { ex }, cursor.Logger);

                    Thread.Sleep((int)Math.Pow(RETRYBACKOFFDELTA, retryCount));
                }
            } while (retryCount < MAXRETRIES);
            if (retryCount == MAXRETRIES)
            {
                LoggerWrapper(MajorEnum.Error, MinorEnum.RetryCountExceeded, "Retry count exceeded for collection {0}", new object[] { collection.Name }, cursor.Logger);
            }
            if (Cache != null)
                Cache.SaveItems(result, collection, cursor.GetSerializer);

            return result;
        }

        #endregion

        #region DB Helpers
        private void ExecutePurgeCmd(string externalID, CollectionType collection, LoggerDelegate logger)
        {
            SqlCommand cmd;

            int retryCount = 0;

            do
            {
                try
                {
                    using (SqlConnection connection = getAndOpenConnection(logger))
                    {
                        //  create command (assumes connection already open)
                        cmd = connection.CreateCommand();
                        cmd.CommandTimeout = 180;

                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.CommandText = "uspPurgeItem0404";
                        cmd.Parameters.AddWithValue("ExternalID", externalID);
                        cmd.Parameters.AddWithValue(COLUMN_COLLECTION_ID, collection.ExternalIDGuid());

                        int count = cmd.ExecuteNonQuery();
                        LoggerWrapper(MajorEnum.Trace, MinorEnum.ItemDeleted, "Item {0} was deleted", new object[] { externalID }, logger);

                        connection.Close();
                    }
                    break;
                }
                catch (Exception ex)
                {
                    retryCount += 1;

                    if (ex.Message.StartsWith("The DataSource specified in the connection is not reachable"))
                        LoggerWrapper(MajorEnum.Warning, MinorEnum.RetryAccessingDataStore, 
                            "Retrying GetItemsFromTheTable because of {0}", new object[] { ex.Message }, logger);
                    else
                        LoggerWrapper(MajorEnum.Error, MinorEnum.ErrorPurgingItems, "Unexpected error in ExecutePurgeCmd: {0}", new object[] { ex }, logger);

                    Thread.Sleep((int)Math.Pow(RETRYBACKOFFDELTA, retryCount));
                }
            } while (retryCount < MAXRETRIES);
            
            if (retryCount == MAXRETRIES)
                LoggerWrapper(MajorEnum.Error, MinorEnum.RetryCountExceeded, "Retry count exceeded for collection {0}", new object[] { collection.Name }, logger);
        }

        private bool ExecuteSaveCmd(SqlCommand cmd, object item, ISerializerType serializer)
        {
            LoggerDelegate logger = serializer.Logger;
            Exception originalException = null;
            int retries = 0;
            while (true)
            {
                try
                {
                    cmd.ExecuteNonQuery();
                    LoggerWrapper(MajorEnum.Trace, MinorEnum.ItemSaved, "Item {0} was saved", new object[] { serializer.GetExternalID(item) }, logger);
                    break;
                }
                catch (SqlException sex)
                {
                    originalException = originalException == null ? sex : originalException;
                    if (++retries > MAXRETRIES)
                    {
                        LoggerWrapper(MajorEnum.Error, MinorEnum.RetryCountExceeded,
                            "retries exceeded saving {0} - original exception - {1} - final exception {2}", 
                            new object[] { serializer.GetExternalID(item), originalException, sex }, logger);
                        return false;
                    }
                    Thread.Sleep((int)Math.Pow(RETRYBACKOFFDELTA, retries));
                }
                finally
                {
                    cmd.Dispose();
                }
            }
            return true;
        }

        private bool PrepareSaveCmd(object item, CollectionType collection, SqlConnection connection, SqlTransaction transaction, ISerializerType serializer)
        {
            try
            {
                LoggerDelegate logger = serializer.Logger;
    
                //  create command (assumes connection already open)
                SqlCommand cmd = CreateCommand(connection, transaction);
                cmd.CommandTimeout = 180;

                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "uspSaveItem0404";
                cmd.Parameters.AddWithValue("PublishedID", MakePublishedID(serializer.GetExternalID(item).ToString()));
                cmd.Parameters.AddWithValue("externalID", serializer.GetExternalID(item).ToString());   //  nvarchar(255),
                cmd.Parameters.AddWithValue(COLUMN_COLLECTION_ID, collection.ExternalIDGuid());         //  uniqueidentifier,
                cmd.Parameters.AddWithValue("itemStatusID", 100);                                       //  bigint,
                cmd.Parameters.AddWithValue("itemIsValid", 0);                                          //  sp adds 1 - old CIPl expects 1 
                //  bigint,
                cmd.Parameters.AddWithValue("transactionID", Guid.NewGuid());                           //  uniqueidentifier,
                cmd.Parameters.AddWithValue("itemTypeVersion", collection.CollectionCompoundType.FullName);
                //  nvarchar(1024)
                cmd.Parameters.AddWithValue("objectText", serializer.GetDataForObject(item));           //  XML,
                cmd.Parameters.AddWithValue("hashValue", getHashValue(serializer.GetDataForObject(item).ToString()));
                //  nvarchar(max),
                return ExecuteSaveCmd(cmd, item, serializer);
            }
            catch (Exception ex)
            {
                LoggerWrapper(MajorEnum.Error, MinorEnum.ErrorSavingItems,
                    "Error encountered saving {0}: {1}", new object[] { item.ToString(), ex }, serializer.Logger);
                return false;
            }
        }

        private bool PrepareSaveIndexEntriesCmd(object item, CollectionType collection, SqlConnection connection, SqlTransaction transaction, ISerializerType serializer)
        {
            try
            {
                if (serializer.GetKeys().Count() == 1)  // just the externalID - can get out now
                    return true;
                LoggerDelegate logger = serializer.Logger;

                //  create command (assumes connection already open)
                SqlCommand cmd = CreateCommand(connection, transaction);
                cmd.CommandTimeout = 180;

                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "uspSaveItemIndexes0404";
                cmd.Parameters.AddWithValue("externalID", serializer.GetExternalID(item).ToString());   //  nvarchar(255),
                cmd.Parameters.AddWithValue(COLUMN_COLLECTION_ID, collection.ExternalIDGuid());         //  uniqueidentifier,

                // build an xml document containing all index entries (empty is OK)

                XElement listElement = new XElement("IndexEntriesList");

                int externalIDPartCount = 0;
                int partCount = 0;
                foreach (KeyValuePair<string, int> kvp in serializer.GetKeys())
                {
                    if (kvp.Value != 0)
                        continue;
                    XElement entryElement = new XElement("IndexEntry");
                    entryElement.Add(new XElement("Pid", kvp.Key));
                    string value = serializer.GetValueForPath(item, "item." + kvp.Key).ToString();
                    entryElement.Add(new XElement("Val", value));
                    listElement.Add(entryElement);
                    externalIDPartCount++;
                }

                // don't include the externalID if there is only one property contributing to the externalid
                if (externalIDPartCount == 1)
                {
                    listElement = new XElement("IndexEntriesList");
                    externalIDPartCount = 0;
                }

                partCount = externalIDPartCount;

                foreach (KeyValuePair<string, int> kvp in serializer.GetKeys())
                {
                    if (kvp.Value == 0)
                        continue;
                    object value = serializer.GetValueForPath(item, "item." + kvp.Key);
                    if (value != null && !string.IsNullOrEmpty(value.ToString()))
                    {
                        XElement entryElement = new XElement("IndexEntry");
                        entryElement.Add(new XElement("Pid", kvp.Key));
                        entryElement.Add(new XElement("Val", value));
                        listElement.Add(entryElement);
                    }
                    partCount++;
                }

                if (partCount == 0)
                    return true;

                cmd.Parameters.AddWithValue("IndexData", listElement.ToString());
                
                return ExecuteSaveCmd(cmd, item, serializer);
            }
            catch (Exception ex)
            {
                LoggerWrapper(MajorEnum.Error, MinorEnum.ErrorSavingItems,
                    "Error encountered saving {0}: {1}", new object[] { item.ToString(), ex }, serializer.Logger);
                return false;
            }
        }

        private Guid MakePublishedID(string value)
        {
            Guid guidValue = default(Guid);
            try
            {
                guidValue = new Guid(value);
            }
            catch (Exception)
            {
                guidValue = Globals.GetHashValue(value);
            }
            return guidValue;
        }

        private void SaveItem(object item, CollectionType collection, ISerializerType serializer)
        {
            LoggerDelegate logger = serializer.Logger;

            int retryCount = 0;

            if (!SetHeaderProperties(item, collection, serializer))
                return;

            do
            {
                using (SqlConnection connection = getAndOpenConnection(logger))
                {
                    using (SqlTransaction transaction = connection.BeginTransaction())
                    {
                        try
                        {
                            PrepareSaveCmd(item, collection, connection, transaction, serializer);

                            PrepareSaveIndexEntriesCmd(item, collection, connection, transaction, serializer);

                            SaveItemsStreamResolver(serializer, collection, connection, transaction, serializer.GetExternalID(item).ToString());

                            transaction.Commit();
                            break;
                        }
                        catch (ArgumentException ex)
                        {
                            if (transaction != null && transaction.Connection != null && transaction.Connection.State == System.Data.ConnectionState.Open)
                                transaction.Rollback();
                            if (ex.Message.StartsWith("The DataSource specified in the conection is not reachable"))
                            {
                                retryCount += 1;
                                if (!LoggerWrapper(MajorEnum.Warning, MinorEnum.RetryAccessingDataStore,
                                    "Retrying after {0}", new object[] { ex.Message }, logger))
                                    throw;
                                Thread.Sleep((int)Math.Pow(RETRYBACKOFFDELTA, retryCount));
                            }
                            else
                                throw;
                        }
                        catch (InvalidOperationException ex)
                        {
                            if (transaction != null && transaction.Connection != null && transaction.Connection.State == System.Data.ConnectionState.Open)
                                transaction.Rollback();
                            if (ex.Message.StartsWith("This SqlTransaction has completed; it is no longer usable"))
                            {
                                retryCount += 1;
                                if (!LoggerWrapper(MajorEnum.Warning, MinorEnum.RetryAccessingDataStore,
                                    "Retrying after {0}", new object[] { ex.Message }, logger))
                                    throw;
                                Thread.Sleep((int)Math.Pow(RETRYBACKOFFDELTA, retryCount));
                            }
                            else
                                throw;
                        }
                        catch (Exception ex)
                        {
                            if (transaction != null && transaction.Connection != null && transaction.Connection.State == System.Data.ConnectionState.Open)
                                transaction.Rollback();

                            LoggerWrapper(MajorEnum.Error, MinorEnum.ErrorSavingItems,
                                "Error encountered saving {0}: {1}", new object[] { item.ToString(), ex }, serializer.Logger);

                            break;
                        }
                    }

                    connection.Close();
                }
            } while (retryCount < MAXRETRIES);
            if (retryCount == MAXRETRIES)
                LoggerWrapper(MajorEnum.Error, MinorEnum.RetryCountExceeded, "Retry count exceeded accessing collection {0}", new object[] { collection.Name }, logger);
        }

        private List<object> populateItemsFromDataReader(SqlDataReader dr, ISerializerType serializer, CollectionType collection, IBooleanValue selectionExoression)
        {
            List<object> result = new List<object>();
            bool bDone = false;

            while (!bDone)
            {
                object item = populateItemFromDataReader(dr, serializer, collection);
                if (item == null)
                {
                    bDone = true;
                }
                else if (CompareProperties(selectionExoression, item, serializer))
                {
                    result.Add(item);
                }
            }

            return result;
        }

        private object populateItemFromDataReader(SqlDataReader dr, ISerializerType serializer, CollectionType collection)
        {
            if (!dr.Read())
            {
                return null;
            }

            string ExternalID = getString(dr, COLUMN_EXTERNAL_ID);
            string ItemTypeVersion = getString(dr, COLUMN_ITEMTYPEVERSION);

            XElement element = XElement.Parse(dr[COLUMN_OBJECT_TEXT].ToString());

            if (!string.IsNullOrEmpty(ItemTypeVersion) && element.Element(XName.Get("ItemTypeVersion", Globals.ciiNS)) == null)
                element.AddFirst(new XElement(XName.Get("ItemTypeVersion", Globals.ciiNS), ItemTypeVersion));

            if (!string.IsNullOrEmpty(ExternalID) && element.Element(XName.Get("ExternalID", Globals.ciiNS)) == null)
                element.AddFirst(new XElement(XName.Get("ExternalID", Globals.ciiNS), ExternalID));

            object result = serializer.GetObjectForData(element);

            // must call serializer before streamResolver as the serializer will pick up the 
            // objects that own the properties that the object's strems have to be bound to
            GetItemsStreamResolver(serializer, collection, ExternalID);

            return result;
        }

        #endregion

        #region Helpers

        // The following is to allow a set of related item updates to be bracketed by a single transaction
        // The following restrictions must be followed:
        // 1. All updates must be against a single database - i.e. distributed transactions are not supported
        // 2. All accesses must be done in such a way that only one retrieval command is active at any one 
        //    point in time - i.e. you cannot nest retrievals in a transaction.

        private void VerifyDatabaseVersion(LoggerDelegate logger)
        {
            SqlConnection connection = getConnection();

            string[] parts = connection.DataSource.Split(new char[] { '\\' });
            if (string.Compare(parts[0], Environment.MachineName, true) != 0 && !PingHost(parts[0], logger))
                throw new ApplicationException(string.Format("Unable to reach database host {0} for DataSource {1}", parts[0], connection.DataSource));

            using (connection = getAndOpenConnection(logger))
            {
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "select cast ([Version] as varchar(2)) + '.' + cast ([Revision] as varchar(2)) from DBVersion";
                try
                {
                    string version = System.Convert.ToString(cmd.ExecuteScalar());
                    DBVersion = int.Parse(version.Split('.')[0]);
                    DBRevision = int.Parse(version.Split('.')[1]);
                    if (DBVersion <= (int)DBVersionValues.Version)
                        return;
                    else
                        LoggerWrapper(MajorEnum.Error, MinorEnum.UnexpectedDatabaseVersion,
                            "Unexpected database version found in {0}. " +
                            "Version found {1}, expecting {2} or less",
                            new object[] { connection.ToString(), DBVersion, (int)DBVersionValues.Version }, logger);
                }
                catch (Exception ex)
                {
                    LoggerWrapper(MajorEnum.Error, MinorEnum.UnexpectedDatabaseVersion,
                        "Unexpected exception interrogating database version in {0}. Exception: {1}",
                        new object[] { connection.ToString(), ex }, logger);
                }
            }
        }

        private SqlConnection getAndOpenConnection(ref SqlTransaction transaction)
        {
            if (transaction == null)
            {
                SqlConnection transactionConnection = new SqlConnection(ConnectionString);
                transactionConnection.Open();
                transaction = transactionConnection.BeginTransaction();
            }
            return transaction.Connection;
        }

        public SqlCommand CreateCommand(SqlConnection connection, SqlTransaction transaction)
        {
            if (connection == null)
                throw new ApplicationException("You must open a connection before creating a command");
            SqlCommand command = connection.CreateCommand();
            if (transaction != null)
            command.Transaction = transaction;
            return command;
        }

        private SqlConnection getConnection()
        {
            SqlConnection connection = new SqlConnection(ConnectionString);
            return connection;
        }

        private SqlConnection getAndOpenConnection(LoggerDelegate logger)
        {
            SqlConnection connection = new SqlConnection(ConnectionString);
            int retryCount = 0;
            while (retryCount < 5)
                if (!PingHost(connection.DataSource, logger))
                    retryCount++;
                else
                    break;
            if (retryCount == 5)
                LoggerWrapper(MajorEnum.Error, MinorEnum.DataSourceNotReachable, 
                    "The DataSource specified in the conection is not reachable: DataSource={0}", new object[] { connection.DataSource }, logger);
            else
                connection.Open();
            return connection;
        }

        private bool PingHost(string host, LoggerDelegate logger)
        {
            string actualHostName = host.TrimStart(new char[] {'\\'}).Split(new char[] { '\\' })[0];
            if (string.Compare(actualHostName, "localhost", true) == 0)
                return true;
            if (string.Compare(actualHostName, Environment.MachineName, true) == 0)
                return true;
            System.Net.NetworkInformation.Ping pinger = new System.Net.NetworkInformation.Ping();
            System.Net.NetworkInformation.PingOptions pingerOptions = new System.Net.NetworkInformation.PingOptions();
            string data = "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";
            byte[] buffer = Encoding.ASCII.GetBytes(data);
            int timeout = 2000;
            System.Net.NetworkInformation.PingReply reply = pinger.Send(actualHostName, timeout, buffer, pingerOptions);
            if (reply.Status == System.Net.NetworkInformation.IPStatus.Success)
                return true;
            else
            {
                LoggerWrapper(MajorEnum.Error, MinorEnum.DataSourceNotReachable, 
                    "Unable to reach database host {0} - reply status {1}", new object[] { host, reply.Status }, logger);
                return false;
            }
        }

        private void CloseConnection(SqlConnection connection)
        {
            try
            {

                if (connection != null)
                {
                    connection.Close();
                }
            }
            catch { }
        }

        private static Int64 getInt64(SqlDataReader dr, string column)
        {
            Int64 result = -1;
            bool isParsed = false;

            //check to see if the column is null before trying to parse
            if (dr[column] != DBNull.Value)
            {
                isParsed = Int64.TryParse(dr[column].ToString(), out result);
            }

            if (!isParsed)
            {
                result = -1;
            }

            return result;
        }

        private static Int32 getInt32(SqlDataReader dr, string column)
        {
            Int32 result = -1;

            //check to see if the column is null before trying to parse
            if (dr[column] != DBNull.Value)
            {
                result = Convert.ToInt32(dr[column].ToString());
            }

            return result;
        }

        private static DateTime getDate(SqlDataReader dr, string column)
        {
            DateTime result = DateTime.MinValue;
            bool isParsed = false;

            if (dr[column] != DBNull.Value)
            {
                isParsed = DateTime.TryParse(dr[column].ToString(), out result);
            }

            if (!isParsed)
            {
                result = DateTime.MinValue;
            }

            return result;
        }

        private static String getString(SqlDataReader dr, string column)
        {
            string result = string.Empty;

            if (dr[column] != DBNull.Value)
            {
                result = dr[column].ToString();
            }

            return result;
        }

        private static Int64 getOptionalInt64(SqlDataReader dr, string column)
        {
            Int64 result = -1;
            bool isParsed = false;

            //check to see if the column is null before trying to parse
            if (dr[column] != DBNull.Value)
            {
                isParsed = Int64.TryParse(dr[column].ToString(), out result);
            }

            if (!isParsed)
            {
                result = -1;
            }

            return result;
        }

        private static Guid? getOptionalGuid(SqlDataReader dr, string column)
        {
            Guid? result = null;

            if (dr[column] != DBNull.Value)
            {
                result = new Guid(dr[column].ToString());
            }
            return result;
        }

        private static Int32 getOptionalInt32(SqlDataReader dr, string column)
        {
            Int32 result = -1;
            bool isParsed = false;

            //check to see if the column is null before trying to parse
            if (dr[column] != DBNull.Value)
            {
                isParsed = Int32.TryParse(dr[column].ToString(), out result);
            }

            if (!isParsed)
            {
                result = -1;
            }

            return result;
        }

        private static DateTime getOptionalDate(SqlDataReader dr, string column)
        {
            DateTime result = DateTime.MinValue;
            bool isParsed = false;

            if (dr[column] != DBNull.Value)
            {
                isParsed = DateTime.TryParse(dr[column].ToString(), out result);
            }

            if (!isParsed)
            {
                result = DateTime.MinValue;
            }

            return result;
        }

        private static String getOptionalString(SqlDataReader dr, string column)
        {
            try
            {
                return dr[column].ToString();
            }
            catch
            {
                return string.Empty;
            }
        }


        /// <summary>
        /// Compresses a string using GZip.  Note:  This is countereffective for strings of less than 3-400 characters or so.
        /// </summary>
        /// <param name="text">Text to compress</param>
        /// <returns>compressed string</returns>
        public static string Compress(string text)
        {
            if (string.IsNullOrEmpty(text))
            {
                return string.Empty;
            }

            byte[] buffer = Encoding.UTF8.GetBytes(text);
            MemoryStream ms = new MemoryStream();
            using (GZipStream zip = new GZipStream(ms, CompressionMode.Compress, true))
            {
                zip.Write(buffer, 0, buffer.Length);
            }
            ms.Position = 0;
            MemoryStream outStream = new MemoryStream();
            byte[] compressed = new byte[ms.Length];
            ms.Read(compressed, 0, compressed.Length);
            byte[] gzBuffer = new byte[compressed.Length + 4];
            System.Buffer.BlockCopy(compressed, 0, gzBuffer, 4, compressed.Length);
            System.Buffer.BlockCopy(BitConverter.GetBytes(buffer.Length), 0, gzBuffer, 0, 4);
            return Convert.ToBase64String(gzBuffer);
        }

        /// <summary>
        /// Decompresses a GZipped-deflated string.
        /// </summary>
        /// <param name="compressedText">compressed text</param>
        /// <returns>decompressed string</returns>
        public static string Decompress(string compressedText)
        {
            if (string.IsNullOrEmpty(compressedText))
            {
                return string.Empty;
            }

            byte[] gzBuffer = Convert.FromBase64String(compressedText);
            using (MemoryStream ms = new MemoryStream())
            {
                int msgLength = BitConverter.ToInt32(gzBuffer, 0);
                ms.Write(gzBuffer, 4, gzBuffer.Length - 4);
                byte[] buffer = new byte[msgLength];
                ms.Position = 0;
                using (GZipStream zip = new GZipStream(ms, CompressionMode.Decompress))
                {
                    zip.Read(buffer, 0, buffer.Length);
                }
                return Encoding.UTF8.GetString(buffer);
            }
        }

        private string getHashValue(string text)
        {
            System.Security.Cryptography.MD5CryptoServiceProvider md5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
            char[] charValue = text.ToCharArray();
            byte[] byteValue = new byte[text.Length * 2];
            string result = string.Empty;

            for (Int32 index = 0; index < charValue.Length; index++)
            {
                byteValue[index * 2] = System.Convert.ToByte((charValue[index] & 0xFF00) >> 8);
                byteValue[index * 2 + 1] = System.Convert.ToByte(charValue[index] & 0xFF);
            }

            result = BitConverter.ToString(md5.ComputeHash(byteValue));
            return result;
        }

        #endregion

        #endregion
    }
}
